package cn.com.chec.en.dao;

import cn.com.chec.en.model.domain.TagInfo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

@Repository
public class TagInfoDao {
	protected static final Logger log = Logger.getLogger(TagInfoDao.class);

	public boolean insert(TagInfo tag) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "insert into tbl_tag_i(tag) values(?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tag.getTag());
			pstmt.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<TagInfo> getSubList(String tag, int start, int num) {
		List<TagInfo> list = new ArrayList<TagInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_tag_i where 1=1 "
					+ (StringUtils.isNotBlank(tag) ? " and tag like ? " : "")
					+ " order by id desc limit ? ,?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			if (StringUtils.isNotBlank(tag)) {
				i++;
				pstmt.setString(i, "%" + tag + "%");
			}
			i++;
			pstmt.setInt(i, start);
			i++;
			pstmt.setInt(i, num);
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResultFromRS(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<TagInfo> getAll() {
		List<TagInfo> list = new ArrayList<TagInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_tag_i ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResultFromRS(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getCount(String tag) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as count from tbl_tag_i where 1=1 "
					+ (StringUtils.isNotBlank(tag) ? " and tag like ? " : "");

			PreparedStatement pstmt = conn.prepareStatement(sql);

			int i = 0;
			if (StringUtils.isNotBlank(tag)) {
				i++;
				pstmt.setString(i, "%" + tag + "%");
			}
			log.info(pstmt.toString());

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public TagInfo getTagById(int id) {
		List<TagInfo> list = new ArrayList<TagInfo>();
		TagInfo info = null;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_tag_i where id = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setInt(i, id);
			ResultSet rs = pstmt.executeQuery();
			list = getResultFromRS(rs);
			info = (list != null) && (list.size() > 0) ? (TagInfo) list.get(0)
					: null;
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return info;
	}

	public boolean del(int id) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "delete from tbl_tag_i where id = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean update(TagInfo tag) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "update tbl_tag_i set tag = ? where id = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tag.getTag());
			pstmt.setInt(2, tag.getId());
			pstmt.executeUpdate();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<TagInfo> getResultFromRS(ResultSet rs) {
		List<TagInfo> list = new ArrayList<TagInfo>();
		try {
			TagInfo info = null;
			while (rs.next()) {
				info = new TagInfo();
				info.setId(rs.getInt("id"));
				info.setTag(rs.getString("tag"));
				list.add(info);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<TagInfo> getTagListByChannelId(int channelId) {
		List<TagInfo> list = new ArrayList<TagInfo>();
		String sql = "select news.news_tag as tag from tbl_news_i news ,tbl_news_channel_rel rel where news.news_id = rel.newsid_fk and rel.channelid_fk = ? and (news.news_tag <> null or  news.news_tag <> '')";
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, channelId);
			ResultSet rs = pstmt.executeQuery();
			List<String> l = new ArrayList<String>();
			while (rs.next()) {
				if (StringUtils.isNotBlank(rs.getString("tag"))) {
					l.add(rs.getString("tag"));
				}
			}
			List<Integer> tagIdList = getTagIdList(l);
			if ((tagIdList != null) && (tagIdList.size() > 0)) {
				for (Integer id : tagIdList) {
					TagInfo ti = getTagById(id.intValue());
					if (ti != null) {
						list.add(ti);
					}
				}
			}
			conn.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	private List<Integer> getTagIdList(List<String> idTagString) {
		List<Integer> result = new ArrayList<Integer>();
		Set<Integer> set = new HashSet<Integer>();
		for (String ids : idTagString) {
			for (String id : ids.split(",")) {
				set.add(Integer.valueOf(id));
			}
		}
		result.addAll(set);
		return result;
	}
}